/*
	EXEC sp_hold_report '0','0','0','0','0','0','0','0','0',1
*/
ALTER   proc sp_hold_report

@Date_From varchar (15),
@Date_To varchar (15),
@Customer_ID varchar(15),
@Product_ID varchar(15),
@region varchar(50),
@rep_office  varchar (50),
@end_user varchar (50),
@sales_person varchar (50),
@defaultPrompt varchar(20),
@order_type int 

as

/*variable to store the values
*/

declare @max_change_no int 
declare @change_date   datetime 
declare @booking_date  datetime 
declare @act_year int 
declare @EndCustomer varchar(65)

declare @so_BK_Mt_I int, @so_Bk_Yr_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int -- Added by Basha (11/21/2003)

declare @date_in datetime
declare @date_out datetime
declare @results Table(Customer_Name varchar(65),Customer_Number varchar(50), Sales_Order_Number varchar(50),Customer_PO varchar(50), SO_Date datetime, Sales_Person varchar(50), Sales_Rep varchar(50), Sales_Hold varchar(10), Marketing_Hold varchar(10),Manufacturing_Hold varchar(10), Finance_Hold varchar(10), Req_Date datetime, Schd_Date datetime, Part_Number varchar(50), Req_Qyt int, Unit_Price float, Status varchar(20), LineItem_No int, Region varchar(50), RepOffice varchar (50), CustomerID int, ProductID int,EndUser varchar(65),ChangeDate datetime , BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,ActYear int, EndCustomer varchar(65))


declare @lineItemCursor CURSOR
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, cool.cool_requested_date, cool.cool_schedule_date, product.part_number, cool.cool_qty, cool.cool_unit_cost, cool.COOL_Status, customer.C_region, sales_order.Regional_Rep, sales_order.Customer_ID, product.Product_ID,
	CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0  ) 
		THEN Customer.C_Name 
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.SO_ODM_ID )
		END AS EndUser,
	CASE WHEN ( SO_ENDCUSTOMER_ID IS NULL OR SO_ENDCUSTOMER_ID <= 0  ) 
		THEN (CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0 ) 
			THEN Customer.C_Name 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  sales_order.SO_ODM_ID )
			END) 
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.SO_ENDCUSTOMER_ID)
		END AS EndCustomer  
	from customer_open_Order_lineItem cool, product, sales_order, customer where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.COOL_Status<> 'SHIPPED' and  cool.COOL_Status<> 'APPROVED' and Sales_Order.SO_Is_Sample = @order_type 	

	declare @customer_Name varchar (65), @customer_number varchar (50), @sales_order_number varchar (50)
	declare @line_item_id int, @customer_po varchar (50), @so_date datetime, @s_person varchar(50), @sales_rep varchar(50)
	declare @req_date datetime, @part_number varchar(50), @quantity int, @unit_cost float, @sch_date datetime
	declare @status varchar (20), @line_item_no int, @temp_region varchar(50), @temp_rep_office varchar(50)
	declare @temp_customer_id int, @temp_product_id int, @temp_end_user varchar (65), @sales_hold varchar(50)
	declare @mktg_hold varchar(50), @mfg_hold varchar(50), @fin_hold varchar(50)
	open @lineItemCursor 
	fetch next from @lineItemCursor into  @line_item_id,@customer_Name,@customer_number,@sales_order_number,@customer_po,@so_date, @s_person, @sales_rep,  @req_date,@sch_date,@part_number, @quantity, @unit_cost, @status, @temp_region, @temp_rep_office,@temp_customer_id, @temp_product_id,@temp_end_user,@EndCustomer

	while @@fetch_status = 0
	begin
		select @max_change_no = isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = @line_item_id
		if ( @max_change_no = 0)
		begin
			set @change_date = null
		end	
		else
		begin
			select @change_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no
		end

		select @booking_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no

		set @so_Bk_Yr_I = DATEPART(yyyy, @booking_date)
		set @act_year = @so_Bk_Yr_I
		set @so_BK_Mt_I = DATEPART(m, @booking_date)

		if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
		begin
			set @so_Bk_Qt_I = 1
			set @so_Bk_Qt_End_I =3
		end
		else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
		begin
			set @so_Bk_Qt_I = 2
			set @so_Bk_Qt_End_I =6
		end
		else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
		begin
			set @so_Bk_Qt_I = 3
			set @so_Bk_Qt_End_I =9
		end						
		else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
		begin
			set @so_Bk_Qt_I = 4
			set @so_Bk_Qt_End_I =12
		end
				
		select @sales_hold = hold_reason_code from sales_hold_table where hold_by_group = 'SALES' and lineitem_id = @line_item_id
		select @mktg_hold = hold_reason_code from sales_hold_table where hold_by_group = 'MARKETING' and lineitem_id = @line_item_id
		select @mfg_hold = hold_reason_code from sales_hold_table where hold_by_group = 'OPERATIONS' and lineitem_id = @line_item_id
		select @fin_hold = hold_reason_code from sales_hold_table where hold_by_group = 'FINANCE' and lineitem_id = @line_item_id
		
		if @sales_hold = '0' 
		begin
			set @sales_hold = 'X' 
		end
		
		if @mktg_hold = '0'
		begin
			set @mktg_hold = 'X' 
		end

		if @mfg_hold = '0'
		begin
			set @mfg_hold = 'X' 
		end	
	
		if @fin_hold = '0'
		begin
			set @fin_hold = 'X' 
		end
		
		declare @shipped_qty int
		declare @lineitem_id int
		  
		select @lineitem_id= lineitem_id from customer_shipping_line_item_table where lineitem_id = @line_item_id and cslit_status = '_SHIPPED_' 
		  
		if (@lineitem_id is not null)
		begin
			select @shipped_qty = sum(cslit_qty_shipped) from customer_shipping_line_item_table where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = '_SHIPPED_' or customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
		      
		      	set @shipped_qty = @quantity-@shipped_qty
			if (@shipped_qty > 0)
	         	begin
				select @line_item_no = COOL_Line_Item_No from customer_open_order_lineitem where LineItem_ID = @line_item_id  
		             insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Part_Number, Req_Qyt, Unit_Price, Status, LineItem_No, Region,RepOffice,CustomerID,ProductID,EndUser,ChangeDate,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear, EndCustomer)
	         	 	values (@customer_Name,@customer_number,@sales_order_number,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, @mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @part_number,@shipped_qty, @unit_cost,@status, @line_item_no, @temp_region,@temp_rep_office,@temp_customer_id,@temp_product_id,@temp_end_user,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@EndCustomer) 
	         	end
		end
		     
		else 
		begin
			select @line_item_no = COOL_Line_Item_No from customer_open_order_lineitem where LineItem_ID = @line_item_id 
			insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Part_Number, Req_Qyt, Unit_Price, Status,LineItem_No,Region,RepOffice,CustomerID,ProductID,EndUser,ChangeDate,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,EndCustomer)
		       	values (@customer_Name,@customer_number,@sales_order_number,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, @mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @part_number,@quantity, @unit_cost,@status,@line_item_no,@temp_region,@temp_rep_office,@temp_customer_id,@temp_product_id,@temp_end_user,@change_date,@so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@EndCustomer) 
		end
		      
		
		select @customer_name = NULL, @customer_number = NULL, @sales_order_number = NULL
		select @line_item_id = NULL, @customer_po = NULL, @so_date = NULL, @s_person = NULL
		select @sales_rep  = NULL, @req_date =NULL, @part_number = NULL, @quantity = NULL
		select @unit_cost = NULL, @sales_hold = NULL, @mktg_hold  = NULL, @mfg_hold = NULL
		select @fin_hold = NULL, @sch_date = NULL, @shipped_qty = NULL, @lineitem_id = NULL
		select @line_item_no = NULL, @temp_region = null, @temp_rep_office = null, @temp_customer_id = null
		select @temp_product_id = null, @temp_end_user = null, @EndCustomer = null

	fetch next from @lineItemCursor into  @line_item_id,@customer_Name,@customer_number,@sales_order_number,@customer_po,@so_date, @s_person, @sales_rep,  @req_date,@sch_date,@part_number, @quantity, @unit_cost, @status, @temp_region, @temp_rep_office,@temp_customer_id,@temp_product_id,@temp_end_user,@EndCustomer
 end
 close @lineItemCursor
 deallocate @lineItemCursor

--select * from @results
select * from @results order by So_Date , BookingYear , BookingQuarter 